/**************************************************************************
	Replication do-file: "Corruption in Customs"
	Cyril Chalendard, Ana Fernandes, Gael Raballand and Bob Rijkers
	
	Created on: 01/07/2022
**************************************************************************/

clear all
set more off, perm
cap log close
cls

* ----------------------- DIRECTORIES AND FOLDERS ----------------------- *

* Directories in which data are stored
global main "PUT YOUR DIRECTORY PATH HERE"
cd "$folder"

* Folders
global outputdata = "$main\Output Data"
cap mkdir "$main\Tables"
global tables = "$main\Tables"

* ----------------------------- BEGINS HERE ----------------------------- *

* -------
* Dataset
* -------
use "$outputdata\CFRR.dta", clear // open dataset

* Sample
drop if wfr==. // drop irrelevant observations


* --------
* Table A4
* --------

* Globals
global coreriskvars "risk tax_rate red_i mixed_dec differentiated valitrade_advice"
global addvars "ln_iwei ln_portwei ln_ival_usd nup_gap_i_int htrl_i_int e20"
global dep "ln_time fraud c1_dlog_val c1_dlog_tax htrl_f_int"
global other "htrl_f_ext htrl_f_adv"

* Matrix
forvalues i = 1(2)3 {
	mat tableA4_`i' = J(31,2,.)
}
forvalues i = 2(2)4 {
	mat tableA4_`i' = J(31,1,.)
}

* Loop for estimations
** Excess interaction shares
local i = 1
local j = 1
foreach x of varlist eis_f eis_nr_f {
	qui: sum `x' if sample_int==1 & eis_f!=. & wfr == 0 // capture summary statistics
	mat tableA4_1[`i',`j'] = `r(mean)'
	mat tableA4_2[`i',`j'] = `r(N)'
	local j = `j' + 1
	mat tableA4_1[`i',`j'] = `r(sd)'
	local j = `j' - 1
	
	qui: sum `x' if sample_int==1 & eis_f!=. & wfr == 1 // capture summary statistics
	mat tableA4_3[`i',`j'] = `r(mean)'
	mat tableA4_4[`i',`j'] = `r(N)'
	local j = `j' + 1
	mat tableA4_3[`i',`j'] = `r(sd)'
	local i = `i' + 1
	local j = `j' - 1
}
** Risk characteristics
local i = 6
local j = 1
foreach x of varlist $coreriskvars {
	qui: sum `x' if sample_int==1 & eis_f!=. & wfr == 0 // capture summary statistics
	mat tableA4_1[`i',`j'] = `r(mean)'
	mat tableA4_2[`i',`j'] = `r(N)'
	local j = `j' + 1
	mat tableA4_1[`i',`j'] = `r(sd)'
	local j = `j' - 1
	
	qui: sum `x' if sample_int==1 & eis_f!=. & wfr == 1 // capture summary statistics
	mat tableA4_3[`i',`j'] = `r(mean)'
	mat tableA4_4[`i',`j'] = `r(N)'
	local j = `j' + 1
	mat tableA4_3[`i',`j'] = `r(sd)'
	local i = `i' + 1
	local j = `j' - 1
}
** Additional characteristics
local i = 14
local j = 1
foreach x of varlist $addvars {
	qui: sum `x' if sample_int==1 & eis_f!=. & wfr == 0 // capture summary statistics
	mat tableA4_1[`i',`j'] = `r(mean)'
	mat tableA4_2[`i',`j'] = `r(N)'
	local j = `j' + 1
	mat tableA4_1[`i',`j'] = `r(sd)'
	local j = `j' - 1
	
	qui: sum `x' if sample_int==1 & eis_f!=. & wfr == 1 // capture summary statistics
	mat tableA4_3[`i',`j'] = `r(mean)'
	mat tableA4_4[`i',`j'] = `r(N)'
	local j = `j' + 1
	mat tableA4_3[`i',`j'] = `r(sd)'
	local i = `i' + 1
	local j = `j' - 1
}
** Main outcomes
local i = 23
local j = 1
foreach x of varlist $dep {
	qui: sum `x' if sample_int==1 & eis_f!=. & wfr == 0 // capture summary statistics
	mat tableA4_1[`i',`j'] = `r(mean)'
	mat tableA4_2[`i',`j'] = `r(N)'
	local j = `j' + 1
	mat tableA4_1[`i',`j'] = `r(sd)'
	local j = `j' - 1
	
	qui: sum `x' if sample_int==1 & eis_f!=. & wfr == 1 // capture summary statistics
	mat tableA4_3[`i',`j'] = `r(mean)'
	mat tableA4_4[`i',`j'] = `r(N)'
	local j = `j' + 1
	mat tableA4_3[`i',`j'] = `r(sd)'
	local i = `i' + 1
	local j = `j' - 1
}
** Additional outcomes
local i = 30
local j = 1
foreach x of varlist $other {
	qui: sum `x' if sample_int==1 & eis_f!=. & wfr == 0 // capture summary statistics
	mat tableA4_1[`i',`j'] = `r(mean)'
	mat tableA4_2[`i',`j'] = `r(N)'
	local j = `j' + 1
	mat tableA4_1[`i',`j'] = `r(sd)'
	local j = `j' - 1
	
	qui: sum `x' if sample_int==1 & eis_f!=. & wfr == 1 // capture summary statistics
	mat tableA4_3[`i',`j'] = `r(mean)'
	mat tableA4_4[`i',`j'] = `r(N)'
	local j = `j' + 1
	mat tableA4_3[`i',`j'] = `r(sd)'
	local i = `i' + 1
	local j = `j' - 1
}


* Export
* ------
putexcel set "$tables\Table A4.xlsx", replace sheet(TableA4) // create a new excel

* Main Title
putexcel (A1:G1), merge hcenter vcenter
putexcel A1 = "During delegated randomization of inspector assignment"

* Secondary titles
putexcel (B2:D2), merge hcenter vcenter bold
putexcel B2 = "Randomized declarations"
putexcel (E2:G2), merge hcenter vcenter bold
putexcel E2 = "Declarations withheld from randomization (corruption suspected)"
local cells `" "B" "C" "D" "E" "F" "G" "'
local labels `" "Average" "Std. dev." "Obs." "Average" "Std. dev." "Obs." "'
forvalues i = 1(1)6 {

	local x: word `i' of `labels'
	local y: word `i' of `cells'

	putexcel `y'3 = "`x'", hcenter vcenter bold
}

* Group of dependend variable's name
local cells `" "5" "9" "26" "'
local labels `" "Excess interaction" "Controls" "Customs outcomes" "'
forvalues i = 1(1)3 {
	
	local x: word `i' of `labels'
	local y: word `i' of `cells'
	
	putexcel (A`y':G`y'), merge hcenter vcenter bold
	putexcel A`y' = "`x'", hcenter vcenter bold
}
** Excess interaction
local k = 6
local labels `" "Excess interaction share" "Excess interaction share - inspector logits" "'
forvalues i = 1(1)2 {
	
	local x: word `i' of `labels'
	
	putexcel A`k' = "`x'", left vcenter
	local k = `k'+1
}
** Controls
local k = 10
local labels `" "Risk characteristics" "Risk score" "Tax rate" "Red channel dummy" "Mixed shipment dummy" "Differentiated share" "Valuation advice dummy" "'
forvalues i = 1(1)7 {
	
	local x: word `i' of `labels'
	
	if "`k'" == "10" {
		putexcel A`k' = "`x'", left vcenter underline
	}
	
	else {
		putexcel A`k' = "`x'", left vcenter
	}
	
	local k = `k'+1
}
local k = 18
local labels `" "Additional characteristics" "Log initial weight" "Log port authority weight" "Log initial value" "Log initial unit price (relative to internal prices)" "Initial hypothetical tax revenue losses (internal prices)" "High potential tax yield dummy" "'
forvalues i = 1(1)7 {
	
	local x: word `i' of `labels'
	
	if "`k'" == "18" {
		putexcel A`k' = "`x'", left vcenter underline
	}
	
	else {
		putexcel A`k' = "`x'", left vcenter
	}
	
	local k = `k'+1
}
** Customs outcomes
local k = 27
local labels `" "Main outcomes" "Log clearance time (hours)" "Fraud record dummy" "\Delta log value" "\Delta log tax" "Hypothetical tax revenue losses (internal prices)" "'
forvalues i = 1(1)6 {
	
	local x: word `i' of `labels'
	
	if "`k'" == "27" {
		putexcel A`k' = "`x'", left vcenter underline
	}
	
	else {
		putexcel A`k' = "`x'", left vcenter
	}
	
	local k = `k'+1
}
local k = 34
local labels `" "Additional outcomes" "Hypothetical tax revenue losses (external reference prices)" "Hypothetical tax revenue losses (valuation advice)" "'
forvalues i = 1(1)3 {
	
	local x: word `i' of `labels'
	
	if "`k'" == "34" {
		putexcel A`k' = "`x'", left vcenter underline
	}
	
	else {
		putexcel A`k' = "`x'", left vcenter
	}
	
	local k = `k'+1
}

* Coefficients
local cells `" "B" "D" "E" "G" "'
forvalues i = 1(1)4 {
	
	local x: word `i' of `cells'
	
	if "`i'" == "1" | "`i'" == "3" {
		putexcel `x'6 = matrix(tableA4_`i'), nformat(0.00) hcenter vcenter
	}
	
	else {
		putexcel `x'6 = matrix(tableA4_`i'), nformat(number_sep) hcenter vcenter
	}
}


* -------------------------------- ENDS HERE -------------------------------- *